Permissions using the WITH GRANT OPTION should be granted only to DBA or application administrator accounts.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-2498	DM5144-SQLServer9	SV-23833r2_rule	ECLP-1	Medium

Description
The WITH GRANT option assigned with privileges, allows the grantee of the privilege to re-grant the privilege to other accounts. Unauthorized or unmanaged assignment of privileges may result in a compromise of data confidentiality and database operation. Privilege assignment should be restricted to DBA, application object owner accounts and application administration accounts.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-22837r2_chk )
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State] FROM sys.database_principals u JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8) LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3 WHERE p.state_desc <> 'DENY' AND p.state IN ('W') ORDER BY u.name, o.name, p.permission_name Review listed object grants. Compare with the list documented in the System Security Plan and authorized by the IAO. If any object grants listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.

Check Text ( C-22837r2_chk )

From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8)
LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE p.state_desc <> 'DENY'
AND p.state IN ('W')
ORDER BY u.name, o.name, p.permission_name

Review listed object grants. Compare with the list documented in the System Security Plan and authorized by the IAO.

If any object grants listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.

Fix Text (F-2479r1_fix)
Revoke unauthorized permissions granted with the WITH GRANT option. From the query prompt: USE [Database Name] REVOKE GRANT OPTION FOR [object] FROM [Principal] You may re-grant the object permissions to the Principal as shown below. From the query prompt: USE [Database Name] GRANT [permission] ON [object] TO [Principal] Document required object permission grants with Grant Option in the System Security Plan and authorize with the IAO.

Fix Text (F-2479r1_fix)

Revoke unauthorized permissions granted with the WITH GRANT option.

From the query prompt:

USE [Database Name]
REVOKE GRANT OPTION FOR [object] FROM [Principal]

You may re-grant the object permissions to the Principal as shown below.

From the query prompt:

USE [Database Name]
GRANT [permission] ON [object] TO [Principal]

Document required object permission grants with Grant Option in the System Security Plan and authorize with the IAO.